package com.fjnu.client.dao;

import com.fjnu.client.entity.vo.*;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.util.List;

public interface IndexDao {

    @Select("SELECT farmer.password FROM iwea_farmer farmer WHERE farmer.login =#{Login}")
    public String getPassword(@Param("Login") int login);

    @Select("SELECT " +
            " farmland1.`name` farmland_name, " +
            " farmer1.`name` farmer_name, " +
            " variety.`name` variety_name, " +
            " batch.growth_status, " +
            " farmland1.id farmland_id, " +
            " farmland1.num, " +
            " farm1.id farm_id " +
            "FROM " +
            " iwea_farmer farmer1, " +
            " iwea_farmland farmland1, " +
            " iwea_farm farm1, " +
            " iwea_plant_history history, " +
            " iwea_plant_batch batch, " +
            " iwea_variety variety, " +
            " ( " +
            "SELECT " +
            " distinct(farm.id) farm_id  " +
            "FROM " +
            " iwea_farmer farmer, " +
            " iwea_farmland farmland, " +
            " iwea_farm farm  " +
            "WHERE " +
            " farmer.id = farmland.farmer  " +
            " AND farmer.login = #{Login}  " +
            " AND farmland.farm_id = farm.id  " +
            " ) tmp " +
            " where " +
            " farm1.id in(tmp.farm_id)   " +
            " and farm1.id = farmland1.farm_id " +
            " and farmland1.farmer = farmer1.id " +
            " and history.farmland_id = farmland1.id " +
            " and history.farm_id = farm1.id " +
            " and batch.id = history.batch_id " +
            " and batch.`status` = 1 " +
            " and batch.plan_id = variety.id " +
            "  and farmland1.num like  #{Direction}")
    public List<IndexFarmlandVO> getIndexFarmland(@Param("Login") int login,@Param("Direction") String direction);

    @Select("SELECT " +
            " ( " +
            "SELECT " +
            " count( history.id )  " +
            "FROM " +
            " iwea_plant_history history, " +
            " iwea_farmer farmer  " +
            "WHERE " +
            " history.farmer = farmer.id  " +
            " AND farmer.login = #{Login} " +
            "  " +
            " ) AS batch_count, " +
            " count( harvest.id ) AS harvest_count, " +
            " sum( harvest.count ) AS output  " +
            "FROM " +
            " iwea_harvest harvest, " +
            " iwea_farmland farmland, " +
            " iwea_farmer farmer  " +
            "WHERE " +
            " harvest.farmland_id = farmland.id  " +
            " AND farmland.farmer = farmer.id  " +
            "AND farmer.login =  #{Login} ")
    public List<ProductionIndexVO> getProductionIndex(@Param("Login") int login);

    @Select("select * from " +
            "(SELECT " +
            " variety.`name` variety_name, " +
            " farmland.`name` farmland_name " +
            "FROM " +
            " iwea_plant_batch batch, " +
            " iwea_plant_history history, " +
            " iwea_farmland farmland, " +
            " iwea_variety variety, " +
            " ( " +
            "SELECT DISTINCT " +
            " ( farm.id ) farm_id  " +
            "FROM " +
            " iwea_farmer farmer, " +
            " iwea_farmland farmland, " +
            " iwea_farm farm  " +
            "WHERE " +
            " farmer.id = farmland.farmer  " +
            " AND farmer.login = #{Login}  " +
            " AND farmland.farm_id = farm.id  " +
            " ) tmp  " +
            "WHERE " +
            " history.farm_id IN ( tmp.farm_id )  " +
            " AND history.batch_id = batch.id  " +
            " AND batch.`status` = #{Status}  " +
            " AND history.farmland_id = farmland.id  " +
            " AND batch.variety_id = variety.id " +
            " ) tmp1, " +
            " (SELECT " +
            " count(DISTINCT(variety.`name`)) as count " +
            "FROM " +
            " iwea_plant_batch batch, " +
            " iwea_plant_history history, " +
            " iwea_farmland farmland, " +
            " iwea_variety variety, " +
            " ( " +
            "SELECT DISTINCT " +
            " ( farm.id ) farm_id  " +
            "FROM " +
            " iwea_farmer farmer, " +
            " iwea_farmland farmland, " +
            " iwea_farm farm  " +
            "WHERE " +
            " farmer.id = farmland.farmer  " +
            " AND farmer.login = #{Login}  " +
            " AND farmland.farm_id = farm.id  " +
            " ) tmp  " +
            "WHERE " +
            " history.farm_id IN ( tmp.farm_id )  " +
            " AND history.batch_id = batch.id  " +
            " AND batch.`status` = #{Status}  " +
            " AND history.farmland_id = farmland.id  " +
            " AND batch.variety_id = variety.id " +
            " ) tmp2")
    public List<CropPlantingVO> getCropPlanting(@Param("Login") int login,@Param("Status") int status);


    @Select("SELECT " +
            " cost.unit, " +
            " ( sum( cost.consumption ) / sum( cost.time_interval ) ) AS per_unit  " +
            "FROM " +
            " iwea_cost cost  " +
            "GROUP BY " +
            " cost.unit")
    public List<ConsumptionIndexVO> getConsumptionIndex();

    @Select("SELECT " +
            " cost.unit, " +
            " sum( cost.consumption ) AS per_month, " +
            " ( sum( cost.consumption ) / sum( cost.time_interval ) ) AS ave  " +
            "FROM " +
            " iwea_cost cost  " +
            "WHERE " +
            " substring_index( cost.cost_time, \"-\", 2 ) = DATE_FORMAT( NOW( ), '%Y-%m' )  " +
            "GROUP BY " +
            " cost.unit")
    public List<ConsumptionIndexMonthVO> getConsumptionIndexMonth();

    @Select(
            "SELECT " +
            " remind.type, " +
            " count( remind.type ) AS count  " +
            "FROM " +
            " iwea_remind remind  " +
            "WHERE " +
            " remind.reading_status = 1  " +
            " AND ( " +
            " remind.farmland_id IN ( " +
            "SELECT " +
            " farmland.id AS farmland_id  " +
            "FROM " +
            " iwea_farm farm, " +
            " iwea_farmland farmland, " +
            " iwea_farmer farmer  " +
            "WHERE " +
            " farm.id = farmland.farm_id  " +
            " AND farmland.farmer = farmer.id  " +
            " AND farmer.login = #{Login} " +
            "  " +
            " )  " +
            " AND ( remind.type = 1 OR remind.type = 2 )  " +
            " )  " +
            " OR ( " +
            "  remind.farm_id IN ( " +
            "  SELECT DISTINCT " +
            "   ( farm.id ) AS farm_id  " +
            "  FROM " +
            "   iwea_farm farm, " +
            "   iwea_farmland farmland, " +
            "   iwea_farmer farmer  " +
            "  WHERE " +
            "   farm.id = farmland.farm_id  " +
            "   AND farmland.farmer = farmer.id  " +
            "   AND farmer.login = #{Login} " +
            "    " +
            "  )  " +
            "  AND remind.type = 3  " +
            " )  " +
            "GROUP BY " +
            "remind.type")
    public List<RemindCountVO> getRemindCount(@Param("Login") int login);

    @Select("SELECT " +
            " remind.*, " +
            " farmland.`name`  " +
            "FROM " +
            " iwea_remind remind, " +
            " iwea_farmland farmland  " +
            "WHERE " +
            " remind.farmland_id = farmland.id  " +
            " AND remind.type = #{Type}  " +
            " AND remind.farmland_id IN ( " +
            "SELECT " +
            " farmland.id AS farmland_id  " +
            "FROM " +
            " iwea_farm farm, " +
            " iwea_farmland farmland, " +
            " iwea_farmer farmer  " +
            "WHERE " +
            " farm.id = farmland.farm_id  " +
            " AND farmland.farmer = farmer.id  " +
            " AND farmer.login = #{Login} " +
            "  " +
            " )")
    public List<RemindDetailVO> getRemindDetail(@Param("Login") int login,@Param("Type") int type);
}
